package database


import kotlinx.serialization.Serializable
import meta.entity.MetaTree
import replaceLast
import java.sql.Connection
import java.sql.DriverManager
import java.sql.ResultSet

enum class SqliteFieldSets {
    Key, Text, Integer, varchar, Real, Float, Double
}

@Serializable
class SqliteProperty(
    override var id: String,
    override var name: String,
    override var ip: String,
    override var port: Int,
    override var username: String,
    override var password: String
) : DataProperty() {
    override fun useProperty(): MutableMap<String, String> {
        return mutableMapOf(
            Pair("id", "sqlitedb"),
            Pair("name", "本地 sqlite 数据库"),
            Pair("ip", "127.0.0.1"),
            Pair("port", "0000"),
            Pair("username", "root"),
            Pair("password", "root"),
            Pair("fetchSize", "1000"),
            Pair("filePath", "jdbc:sqlite:src/main/resources/core.db"),
        )
    }
}

data class SqliteFieldJson (
    var fieldName: String,
    var fieldType: SqliteFieldSets,
    var isPrimary:Boolean = false,
    var isUnique: Boolean = false,
    var isNull: Boolean = true,
    var isForeignKey: Boolean = false,
    var foreignName: String = "",
    var foreignTable: String = "",
    var foreignColumn: String = ""
)

fun List<SqliteFieldJson>.tableCreateSql(tableName: String): String {
    var expression = "Create table if not exists $tableName ("
    var body = ""
    this.forEachIndexed { index, field -> //主键只有1个，并且默认第一个
        if(field.isPrimary) body = "${field.fieldName} ${field.fieldType} primary key autoincrement"
        else if(field.isForeignKey) body = "constraint ${field.foreignName} foreign key(${field.foreignColumn}) references ${field.foreignTable}(${field.foreignColumn})"
        else {
            val unique = if(field.isUnique) " Unique" else "" //是否唯一
            val nullType = if(field.isNull) "" else " not null" //是否为空
            body = "${field.fieldName} ${field.fieldType}$unique$nullType"
        }
        println(body)
        if(index == this.size - 1) expression += "$body);"
        else expression += "$body,"
    }
    return expression
}


fun List<String>.join(connector: String): String {
    var result = ""
    this.forEachIndexed { index, s -> result += "$s $connector "}
    return result.replaceLast(connector, "")
}

fun ResultSet.columnNames(): List<String> {
    val names = mutableListOf<String>()
    val count = this.metaData.columnCount
    for(i in 1 until count){ //注意从1开始循环
        names.add(this.metaData.getColumnName(i))
    }
    return names
}


fun ResultSet.toJsonList(): List<HashMap<String, Any>> {
    val result = mutableListOf<HashMap<String, Any>>()
    val columnNames = this.columnNames()
    while (this.next()){
        val columnMap = HashMap<String, Any>()
        columnNames.forEach {
            columnMap.put(it, this.getObject(it))
        }
        result.add(columnMap)
    }
    return result
}

//                println(property.name)
//                property.isAccessible = true
//                try {
//                    when (property.returnType) {
//                        Float::class.createType() -> {
//                            property as KMutableProperty1<Any, Float>
//                            property.set(inst, this.getFloat(property.name))
//                        }
//                        String::class.createType() -> {
//                            println("aaaaaaaaaaaaaaa")
//                            property as KMutableProperty1<Any, String>
//                            property.set(inst, this.getString(property.name))
//                            println(this.getString(property.name))
//                        }
//                    }
//                }catch (e: Exception){
//                    println(e)
//                }

fun ResultSet.toEntity(): List<Any> {
    while (this.next()){
        val id = this.getInt("customerId")
        val name = this.getString("name")
        println("id=$id, name=$name")
    }
    return listOf<MetaTree>()
}

class SqliteFactory(override val dataProperty: DataProperty) : DataMaster {
    private var sqliteConn: Connection
    private val dp = dataProperty.useProperty()

    init {
        Class.forName("org.sqlite.JDBC")
        sqliteConn = DriverManager.getConnection(dp["filePath"])
    }

    fun connect(): Boolean {
        return true
    }

    fun createDataBase(name: String): Boolean {
        sqliteConn = DriverManager.getConnection("jdbc:sqlite:src/main/resources/$name.db")
        return true
    }

    fun deleteDataBase(name: String) : Boolean {
        return true
    }

    fun createTable(name: String, fields: List<SqliteFieldJson>) {
        val stmt = sqliteConn.createStatement()
        val sql = fields.tableCreateSql(name)
        println(sql)
        stmt.executeUpdate(sql)
        stmt.close()
    }

    fun dropTable(name: String) {
        val stmt = sqliteConn.createStatement()
        val sql = "drop table $name"
        stmt.executeUpdate(sql)
        stmt.close()
    }

    fun insertRecord(tableName: String, records: Map<String, Any>) {
        val stmt = sqliteConn.createStatement()
        val names = records.keys.joinToString(",")
        val values = records.values.joinToString(",")
        val sql = "insert into $tableName ($names) values ($values)"
        println(sql)
        stmt.executeUpdate(sql)
        stmt.close()
    }

    fun updateRecord(tableName: String, newVals: Map<String, Any>, cdts: Map<String, Any>) {
        val stmt = sqliteConn.createStatement()
        val valueExp = mutableListOf<String>()
        newVals.forEach { (k, v) ->
            valueExp.add("$k = $v")
        }
        val conditionExp = mutableListOf<String>()
        cdts.forEach { (k, v) ->
            conditionExp.add("$k $v")
        }
        val sql = "update $tableName set ${valueExp.join(",")} where ${conditionExp.join("and")}"
        stmt.executeUpdate(sql)
    }

    fun deleteRecord(tableName: String, cdts: Map<String, Any>) {
        val stmt = sqliteConn.createStatement()
        val conditionExp = mutableListOf<String>()
        cdts.forEach { (k, v) ->
            conditionExp.add("$k $v")
        }
        val sql = "delete from $tableName where ${conditionExp.join("and")}"
        stmt.executeUpdate(sql)
    }

    fun queryRecords(tableName: String, cdts: Map<String, Any>): List<HashMap<String, Any>> {
        val stmt = sqliteConn.createStatement()
        val conditionExp = mutableListOf<String>()
        cdts.forEach { (k, v) ->
            conditionExp.add("$k $v")
        }
        val exp = if(cdts.size > 0) "where ${conditionExp.join("and")}" else ""
        val sql = "select * from $tableName $exp"
        println(sql)
        return stmt.executeQuery(sql).toJsonList()
    }

    fun executeRawSql(sql:String) {
        val stmt = sqliteConn.createStatement()
        stmt.executeUpdate(sql)
        stmt.close()
    }
}


fun main() {

}